package com.class5.springboot.frame.repo.real;

import com.class5.springboot.frame.repo.*;
import com.class5.springboot.frame.repo.dto.PageData;
import com.class5.springboot.frame.repo.entity.SuperEntity;

import java.io.InputStream;
import java.sql.*;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.Map;


import com.class5.springboot.frame.repo.config.RepositoryProperties;
import com.class5.springboot.frame.repo.exception.RepositoryException;
import com.class5.springboot.frame.repo.real.init.DbProjectInitBean;
import com.class5.springboot.frame.repo.tool.xml.DbProject;
import com.class5.springboot.frame.repo.tool.xml.DbProjectFactory;
import com.class5.springboot.util.CollectionUtil;
import com.google.common.base.Preconditions;
import org.apache.commons.beanutils.PropertyUtils;
import org.springframework.beans.factory.annotation.Autowired;

import com.alibaba.fastjson.JSONObject;
import com.mysql.jdbc.Statement;

import lombok.extern.slf4j.Slf4j;
import org.apache.commons.beanutils.PropertyUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.ColumnMapRowMapper;
import org.springframework.jdbc.core.ConnectionCallback;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.TransactionStatus;
import org.springframework.transaction.support.TransactionCallback;
import org.springframework.transaction.support.TransactionSynchronizationManager;
import org.springframework.transaction.support.TransactionTemplate;

/**
 * 数据库操作
 *
 * @author Administrator
 *
 */
@Slf4j
@Repository
public class NormalRepository implements INormalRepository{
    @Autowired
    private RepositoryProperties config;
    //数据库操作对象
    private JdbcTemplate writeableTemplate;
    private JdbcTemplate readOnlyTemplate;
    //事务模板对象
    @Autowired
    private TransactionTemplate trxTpl;

    public NormalRepository(JdbcTemplate writeableTemplate, JdbcTemplate readOnlyTemplate) {
        this.writeableTemplate = writeableTemplate;
        this.readOnlyTemplate = readOnlyTemplate;
    }
    //根据当前事务是否只读来决定返回那一个数据源
    private JdbcTemplate getJt(boolean hasUpdate) {
        boolean flag = TransactionSynchronizationManager.isCurrentTransactionReadOnly();
        if(flag) {
            if(hasUpdate) {
                throw new RepositoryException("只读模式下不能进行数据更新");
            }
            return this.readOnlyTemplate;
        }
        return writeableTemplate;
    }
    /* (non-Javadoc)
     * @see com.teynet.frame.repo.real.core.INormalRepository#insert(T)
     */
    @Override
    public <T> T insert(T entity) {
        Preconditions.checkNotNull(entity);
        long begin = System.currentTimeMillis();
        EntityMapper em = EntityMapperFactory.get(entity.getClass());
        BatchSql info = em.generatorBatchInsert(Arrays.asList(new Object[]{entity}));
        KeyHolder holder = new GeneratedKeyHolder();
        JdbcTemplate jt = getJt(true);
        jt.update(new PreparedStatementCreator() {
            @Override
            public PreparedStatement createPreparedStatement(Connection con)
                    throws SQLException {
                PreparedStatement ps = con.prepareStatement(info.getSql(), Statement.RETURN_GENERATED_KEYS);
                Object[] params = info.getBatchParams().get(0);
                if(params != null){
                    for(int i=0; i<params.length; i++){
                        ps.setObject(i+1, params[i]);
                    }
                }
                return ps;
            }
        }, holder);
        if(holder.getKey() != null){
            long id = holder.getKey().longValue();
            try {
                PropertyUtils.setProperty(entity, em.getIdField().getName(), id);
            } catch (Exception e) {
                throw new RepositoryException("Set id value error", e);
            }
        }
        log.debug("Execute Sql:{},useTime:{}ms", info, System.currentTimeMillis()-begin);
        return entity;
    }
    /* (non-Javadoc)
     * @see com.teynet.frame.repo.real.core.INormalRepository#insertOrUpdate(T)
     */
    @Override
    public <T> T insertOrUpdate(T entity){
        if(entity == null)
            return null;

        Object idval = null;
        try {
            idval = PropertyUtils.getProperty(entity, "id");
        } catch (Exception e) {
            throw new RepositoryException("获取" + entity.getClass().getName() + "id属性失败", e);
        }
        if(idval == null){
            return insert(entity);
        }
        return update(entity);
    }
    /* (non-Javadoc)
     * @see com.teynet.frame.repo.real.core.INormalRepository#batchInsert(java.util.List)
     */
    @Override
    public <T> List<T> batchInsert(List<T> entities){
        if(entities == null || entities.isEmpty()){
            throw new RepositoryException("Param entities at least one element!");
        }
        JdbcTemplate jt = getJt(true);
        long begin = System.currentTimeMillis();
        EntityMapper em = EntityMapperFactory.get(entities.get(0).getClass());
        BatchSql info = em.generatorBatchInsert(entities);
        return jt.execute(new ConnectionCallback<List<T>>() {
            @Override
            public List<T> doInConnection(Connection con) throws SQLException,
                    DataAccessException {
                String[] columnNames= {em.getIdField().getColName()} ;
                PreparedStatement pstmt = con.prepareStatement(info.getSql(), columnNames) ;
                for(Object[] row : info.getBatchParams()){
                    for(int i=0; i<row.length; i++){
                        pstmt.setObject(i+1, row[i]);
                    }
                    pstmt.addBatch();
                }
                pstmt.executeBatch();
                ResultSet rs = pstmt.getGeneratedKeys() ;
                int i = 0, rowCount = entities.size();
                while(rs.next() && i < rowCount){
                    try {
                        PropertyUtils.setProperty(entities.get(i), em.getIdField().getName(), rs.getLong(1));
                    } catch (Exception e) {
                        throw new RepositoryException("Set entity id value error", e);
                    }
                    i++ ;
                }
                log.debug("Execute batch update sql:{},useTime:{}ms", info.getSql(), System.currentTimeMillis()-begin);
                return entities;
            }
        });
    }
    /* (non-Javadoc)
     * @see com.teynet.frame.repo.real.core.INormalRepository#update(T)
     */
    @Override
    public <T> T update(T entity) {
        JdbcTemplate jt = getJt(true);
        long begin = System.currentTimeMillis();
        EntityMapper em = EntityMapperFactory.get(entity.getClass());
        Long id = (Long) em.getIdField()
                .getValue(entity);
        //考虑是否需要乐观锁，如果实体中没有版本号，从数据库取
        Long oldver = null;
        if(em.getRefEntityMapper().getVersionField() != null){
            if(em.getVersionField() != null)
                oldver = em.getVersionField().getValue(entity);
            if(oldver == null){
                oldver = jt.queryForObject("select " + em.getRefEntityMapper().getVersionField().getColName() + " from "
                        + em.getRefEntityMapper().getTableName() + " where "
                        + em.getRefEntityMapper().getIdField().getColName() + "=" + id, Long.class);
            }
        }
        //未修改则不更新
        if(entity instanceof SuperEntity){
            if(!((SuperEntity)entity).isModified()){
                log.info("entity bbject is note modify");
                return entity;
            }
        }
        Sql info = em.generatorUpdateById(entity, id, oldver, false);
        int num = jt.update(info.getSql(), info.getParams());
        if(oldver != null && num == 0){
            throw new RuntimeException("Update error current version is:" + oldver + ", but not found record matached!");
        }
        log.debug("Execute Sql:{},useTime:{}ms", info, System.currentTimeMillis()-begin);
        return entity;
    }
    /* (non-Javadoc)
     * @see com.teynet.frame.repo.real.core.INormalRepository#batchUpdate(java.util.List)
     */
    @Override
    public <T> List<T> batchUpdate(List<T> entities){
        if(entities == null || entities.isEmpty()){
            throw new RepositoryException("Param entities at least one element!");
        }
        JdbcTemplate jt = getJt(true);
        long begin = System.currentTimeMillis();
        EntityMapper em = EntityMapperFactory.get(entities.get(0).getClass());
        BatchSql info = em.generatorBatchUpdate(entities);
        jt.batchUpdate(info.getSql(), info.getBatchParams());
        log.debug("Execute batch update sql:{},useTime:{}ms", info.getSql(), System.currentTimeMillis()-begin);
        return entities;
    }

    /* (non-Javadoc)
     * @see com.teynet.frame.repo.real.core.INormalRepository#remove(java.lang.Object)
     */
    @Override
    public int remove(Object entity) {
        EntityMapper em = EntityMapperFactory.get(entity.getClass());
        return removeById(em.getRefEntityMapper().getEntityClass(), (Long) em.getRefEntityMapper().getIdField().getValue(entity));
    }
    /* (non-Javadoc)
     * @see com.teynet.frame.repo.real.core.INormalRepository#batchRemove(java.util.List)
     */
    @Override
    public int batchRemove(List<?> entities) {
        if(entities == null || entities.isEmpty())
            return 0;
        Class<?> cla = entities.get(0).getClass();
        EntityMapper em = EntityMapperFactory.get(cla);
        List<Long> ids = new ArrayList<Long>();
        for(Object entity : entities){
            ids.add(em.getIdField().getValue(entity));
        }
        return batchRemoveByIds(cla, ids);
    }
    /* (non-Javadoc)
     * @see com.teynet.frame.repo.real.core.INormalRepository#removeById(java.lang.Class, java.lang.Long)
     */
    @Override
    public int removeById(Class<?> entityClass, Long id) {
        return batchRemoveByIds(entityClass, Arrays.asList(id));
    }
    /* (non-Javadoc)
     * @see com.teynet.frame.repo.real.core.INormalRepository#batchRemoveByIds(java.lang.Class, java.util.List)
     */
    @Override
    public int batchRemoveByIds(Class<?> entityClass, List<Long> ids) {
        JdbcTemplate jt = getJt(true);
        long begin = System.currentTimeMillis();
        EntityMapper em = EntityMapperFactory.get(entityClass);
        BatchSql info = em.generatorBatchRemoveById(ids);
        if(info == null)
            return 0;
        if(info.getBatchParams().isEmpty()){
            return 0;
        }
        int[] nums = jt.batchUpdate(info.getSql(), info.getBatchParams());
        int num = 0;
        for(int num2 : nums){
            num = num + num2;
        }
        log.debug("Execute Sql:{},useTime:{}ms", info.getSql(), System.currentTimeMillis()-begin);
        return num;
    }
    /* (non-Javadoc)
     * @see com.teynet.frame.repo.real.core.INormalRepository#getUnique(java.lang.String, java.lang.Object, java.lang.Class)
     */
    @Override
    @SuppressWarnings("unchecked")
    public <T> T getUnique(String colName,Object value, Class<T> entityClass) {
        JdbcTemplate jt = getJt(false);
        Preconditions.checkNotNull(colName);
        Preconditions.checkNotNull(value);
        long begin = System.currentTimeMillis();
        EntityMapper em = EntityMapperFactory.get(entityClass);
        if (!em.getFieldMap().containsKey(colName)) {
            throw new RepositoryException(entityClass.getName()+" missing column, colName as "+colName);
        }
        String colstr = em.getColumnJoinsFragment(null);
        String sql = String.format("SELECT %s FROM %s WHERE %s = ?", colstr, em.getTableName(), colName);
        List<?> ls = jt.query(sql, new Object[]{value}, em.getJdbcRowMapper());
        log.debug("Execute Sql:{},useTime:{}ms", sql, System.currentTimeMillis()-begin);
        return ls.isEmpty()?null:(T)ls.get(0);
    }
    /* (non-Javadoc)
     * @see com.teynet.frame.repo.real.core.INormalRepository#get(java.lang.Long, java.lang.Class)
     */
    @Override
    @SuppressWarnings("unchecked")
    public <T> T get(Long id, Class<T> entityClass) {
        JdbcTemplate jt = getJt(false);
        Preconditions.checkNotNull(id);
        long begin = System.currentTimeMillis();
        EntityMapper em = EntityMapperFactory.get(entityClass);
        String colstr = em.getColumnJoinsFragment(null);
        String sql = String.format("SELECT %s FROM %s WHERE %s = ?", colstr, em.getTableName(), em.getIdField().getColName());
        List<?> ls = jt.query(sql, new Object[]{id}, em.getJdbcRowMapper());
        log.debug("Execute Sql:{},useTime:{}ms", sql, System.currentTimeMillis()-begin);
        return ls.isEmpty()?null:(T)ls.get(0);
    }
    /*private Class<?> getClass(String name){
        try {
            return Class.forName(name);
        } catch (Exception e) {
            throw new RepositoryException(e);
        }
    }*/
    /* (non-Javadoc)
     * @see com.teynet.frame.repo.real.core.INormalRepository#queryForObject(com.teynet.frame.repo.real.core.Sql)
     */
    @Override
    @SuppressWarnings("unchecked")
    public Object queryForObject(Sql sql){
        sql = convert(sql);
        //log.debug("Before execute {}", sql);
        long begin = System.currentTimeMillis();
        log.debug("QueryForObject Execute Sql:{}", sql);
        //转换成其它对象
        Sql sql2 = new Sql(sql.getSql() + " limit 0, 1", sql.getParams());
        List<Map<String, Object>> ls = query(sql2, Map.class);
        if(ls == null || ls.isEmpty()){
            return null;
        }
        log.debug("UseTime:" + (System.currentTimeMillis()-begin));
        return ls.get(0).values().iterator().next();
    }
    private boolean hasUpdate(Sql sql) {
        String str = sql.getSql().toLowerCase().trim();
        return str.startsWith("insert") || str.startsWith("update") || str.startsWith("delete");
    }
    /* (non-Javadoc)
     * @see com.teynet.frame.repo.real.core.INormalRepository#queryForObject(com.teynet.frame.repo.real.core.Sql, java.lang.Class)
     */
    @Override
    @SuppressWarnings("unchecked")
    public <T> T queryForObject(Sql sql, Class<T> entityClass){
        JdbcTemplate jt = getJt(hasUpdate(sql));
        sql = convert(sql);
        //log.debug("Before execute {}", sql);
        long begin = System.currentTimeMillis();
        //如果entityClass为实体类
        T rdata = null;
        log.debug("QueryForObject Execute Sql:{}", sql);
        if(entityClass.isAssignableFrom(Map.class)){
            //转换成其它对象
            Sql sql2 = new Sql(sql.getSql() + " limit 0, 1", sql.getParams());
            List<Map<String, Object>> ls = query(sql2, Map.class);
            if(ls == null || ls.isEmpty()){
                return null;
            }
            log.debug("UseTime:" + (System.currentTimeMillis()-begin));
            return (T)ls.get(0);
        }else{
            EntityMapper em = EntityMapperFactory.get(entityClass);
            if(em == null)
                throw new RepositoryException("Not found entity class " + entityClass);
            List<Object> rList = jt.query(sql.getSql(),sql.getParams(),em.getJdbcRowMapper());
            if(CollectionUtil.isNotEmpty(rList)){
                rdata = (T) rList.get(0);
            }
            log.debug("UseTime:" + (System.currentTimeMillis()-begin));
            return rdata;
        }
    }

    /* (non-Javadoc)
     * @see com.teynet.frame.repo.real.core.INormalRepository#queryForLong(com.teynet.frame.repo.real.core.Sql)
     */
    @Override
    public Long queryForLong(Sql sql){
        Object obj = queryForObject(sql);
        if(obj == null)
            return null;
        return (obj instanceof Long)?(Long)obj:Long.parseLong(obj+"");
    }
    /* (non-Javadoc)
     * @see com.teynet.frame.repo.real.core.INormalRepository#queryForDouble(com.teynet.frame.repo.real.core.Sql)
     */
    @Override
    public double queryForDouble(Sql sql){
        Object obj = queryForObject(sql);
        if(obj == null)
            return 0d;
        return (obj instanceof Double)?(Double)obj:Double.parseDouble(obj+"");
    }
    /* (non-Javadoc)
     * @see com.teynet.frame.repo.real.core.INormalRepository#queryForInt(com.teynet.frame.repo.real.core.Sql)
     */
    @Override
    public int queryForInt(Sql sql){
        Object obj = queryForObject(sql);
        if(obj == null)
            return 0;
        return (obj instanceof Integer)?(Integer)obj:Integer.parseInt(obj+"");
    }
    /* (non-Javadoc)
     * @see com.teynet.frame.repo.real.core.INormalRepository#query(com.teynet.frame.repo.real.core.Sql, java.lang.Class)
     */
    @Override
    @SuppressWarnings("rawtypes")
    public List query(Sql sql, Class<?> dataClass){
        JdbcTemplate jt = getJt(hasUpdate(sql));
        sql = convert(sql);
        long begin = System.currentTimeMillis();
        List rdata = null;
        EntityMapper em = EntityMapperFactory.get(dataClass);
        if(em != null){
            rdata = jt.query(sql.getSql(), sql.getParams(), em.getJdbcRowMapper());
        }else{
            rdata = jt.query(sql.getSql(), sql.getParams(), new ColumnMapRowMapper());
        }
        log.debug("Query Execute Sql:{},useTime:{}ms", sql, System.currentTimeMillis()-begin);
        return rdata;
    }
    /* (non-Javadoc)
     * @see com.teynet.frame.repo.real.core.INormalRepository#queryForPage(com.teynet.frame.repo.real.core.Sql, java.lang.Class, int, int)
     */
    @Override
    @SuppressWarnings("rawtypes")
    public PageData queryForPage(Sql sql, Class<?> dataClass, int pageNum, int pageSize){
        JdbcTemplate jt = getJt(hasUpdate(sql));
        sql = convert(sql);
        long begin = System.currentTimeMillis();
        int page = pageNum < 1?1:pageNum;
        int size = pageSize < 1?20:pageSize;
        int start = (page-1) * size;
        String pageSql = sql.getSql();
        pageSql = pageSql + " limit " + start + "," + size;
        List listData = null;
        EntityMapper em = EntityMapperFactory.get(dataClass);
        if(em != null){
            listData = (List)jt.query(pageSql, sql.getParams(), em.getJdbcRowMapper());
        }else{
            listData = jt.query(pageSql, sql.getParams(), new ColumnMapRowMapper());
        }
        int total = 0;
        //总记录数
        String vstr = sql.getSql().toLowerCase();
        int fromStarta = vstr.indexOf("\nfrom");
        int fromStartb = vstr.indexOf(" from");
        int from = fromStarta;
        if(from < 0 || (fromStartb > 0 && fromStartb < from)){
            from =fromStartb;
        }
        String tsql = "SELECT COUNT(*) " + sql.getSql().substring(from);
        int sortstart = tsql.toLowerCase().indexOf("order by");
        if(sortstart > 0){
            tsql = tsql.substring(0, sortstart);
        }
        Sql tsqlobj = Sql.get(tsql, sql.getParams());
        //log.debug("TSQL:{}", tsqlobj);
        total = queryForInt(tsqlobj);

        log.debug("Execute page sql:{},useTime:{}ms", pageSql, System.currentTimeMillis()-begin);
        return new PageData(listData, page, size, total);
    }
    /**
     * 对其中的实体类进行替代
     * @param sql
     * @return
     */
    private Sql convert(Sql sql){
        if(sql == null){
            throw new RepositoryException("SQL语句为空");
        }
        String sqlstr = sql.getSql();
        if(sqlstr.indexOf("#{") >=0){
            int start = sqlstr.indexOf("#{");
            StringBuffer sb = new StringBuffer();
            while(start >= 0){
                sb.append(sqlstr.substring(0, start));
                int end = sqlstr.indexOf("}", start);
                String exp = sqlstr.substring(start+1, end+1);
                JSONObject obj = JSONObject.parseObject(exp);
                sb.append(convert(obj));
                sqlstr = sqlstr.substring(end+1);
                start = sqlstr.indexOf("#{");
            }
            sb.append(sqlstr);
            return new Sql(sb.toString(), sql.getParams());
        }
        return sql;
    }
    private String convert(JSONObject obj){
        if("select".equals(obj.getString("opt"))){
            String cla = obj.getString("cla");
            String prefix = obj.getString("prefix");
            if(prefix != null && prefix.trim().equals("")){
                prefix = null;
            }
            EntityMapper em = null;
            try {
                em = EntityMapperFactory.get(Class.forName(cla));
            } catch (Exception e) {
                throw new RepositoryException("未找到实体类" + cla, e);
            }
            return String.format("SELECT %s FROM %s", em.getColumnJoinsFragment(prefix), em.getTableName()+(prefix==null?"":" "+prefix));
        }
        return null;
    }
    /* (non-Javadoc)
     * @see com.teynet.frame.repo.real.core.INormalRepository#execute(com.teynet.frame.repo.real.core.Sql)
     */
    @Override
    public int execute(Sql sql){
        JdbcTemplate jt = getJt(hasUpdate(sql));
        return jt.update(sql.getSql(), sql.getParams());
    }
    @Override
    public <T> List<T> query(Sql sql, ExtractRowCallback<T> extract) {
        JdbcTemplate jt = getJt(hasUpdate(sql));
        List<T> ls = jt.query(sql.getSql(), sql.getParams(), new ResultSetExtractor<List<T>>(){
            @Override
            public List<T> extractData(ResultSet rs) throws SQLException, DataAccessException {
                List<T> rls = new ArrayList<>();
                int index = 0;
                while(rs.next()) {
                    try {
                        T obj = extract.extract(rs, index++);
                        if(obj != null) {
                            rls.add(obj);
                        }
                    } catch (Exception e) {
                        throw new SQLException(e);
                    }
                }
                return rls;
            }
        });
        return ls;
    }
    @Override
    public <T> T queryForObject(Sql sql, ExtractDataCallback<T> callback) {
        JdbcTemplate jt = getJt(hasUpdate(sql));
        T obj = jt.query(sql.getSql(), sql.getParams(), new ResultSetExtractor<T>(){
            @Override
            public T extractData(ResultSet rs) throws SQLException, DataAccessException {
                try {
                    return callback.extractData(rs);
                }catch(Exception e) {
                    throw new SQLException(e);
                }
            }
        });
        return obj;
    }
    /**
     * 根据XML模型更新数据库表结构
     * @param stream
     */
    @Override
    public void initSchema(InputStream stream) {
        JdbcTemplate jt = getJt(false);
        DbProject project = DbProjectFactory.get().build(stream);
        DbProjectInitBean bean = new DbProjectInitBean(jt);
        if("true".equals(config.getNeedUpdateScheme())){
            List<String> sqls = bean.generateDatabaseUpdateSql(project, config.getDataUpdateType());
            if(!sqls.isEmpty()) {
                log.info("Begin update database {}", project.getProject());
                bean.updateDatabase(sqls);
                log.info("End update database {}", project.getProject());
            }
        }else{
            log.info("myconfig.dbconfig.needUpdateScheme为false，跳过数据库更新.");
        }
    }
    @Override
    public List<String> getModifyDdl(String project) {
        DbProject prj = DbProjectFactory.get(project);
        if(prj == null)
            throw new RepositoryException("未找到数据库配置" + project);
        JdbcTemplate jt = getJt(false);
        DbProjectInitBean bean = new DbProjectInitBean(jt);
        return bean.generateDatabaseUpdateSql(prj, config.getDataUpdateType());
    }
    /**
     * 将某个项目的XML配置与数据库进行对比，生成相关的drop的ddl语句，供DBA做数据库清理使用
     */
    @Override
    public List<String> getDropDdl(String project){
        DbProject prj = DbProjectFactory.get(project);
        if(prj == null)
            throw new RepositoryException("未找到数据库配置" + project);
        JdbcTemplate jt = getJt(false);
        DbProjectInitBean bean = new DbProjectInitBean(jt);
        return bean.getDropedDdl(prj);
    }

    @Override
    public <T> T executeInTransaction(ExecuteInTransactionCallback<T> callback) {
        return trxTpl.execute(new TransactionCallback<T>() {
            @Override
            public T doInTransaction(TransactionStatus status) {
                try{
                    return callback.execute(NormalRepository.this);
                }catch(Throwable e) {
                    status.setRollbackOnly();
                    if(e instanceof RepositoryException) {
                        throw (RepositoryException)e;
                    }
                    throw new RepositoryException(e);
                }
            }
        });
    }

}
